<cfcomponent extends="Controller">

	<cffunction name="index">		
		<cfset tablesToCreate = "players,teams,teamplayers,fixtures,results,playerresults,seasons,ladders">
	</cffunction>
	
	<cffunction name="alterTable">
		<cfsavecontent variable="data">
			
			<cfif isDefined("params.table") AND isDefined("params.field")>
			
					<cfoutput>
						Altering '#params.table#' table, adding col...
					</cfoutput>
					<cfquery name="qCustomAlter" datasource="default">
						ALTER TABLE #params.table# ADD #params.field#
					</cfquery>
					Completed<br />
					<cfif isDefined("params.showDetails")>
						<cfquery name="qCustomSelect" datasource="default">
							SELECT * from #params.table#;
						</cfquery>
						<br />
						<cfdump var="#qCustomSelect#" label="Test Data">
						
					</cfif>
					<!---<cfif isDefined("params.remove")>
						<cftry>
						yes
						<cfoutput>
							Altering '#params.table#' table, removing col...
						</cfoutput>
						<cfquery name="qCustomAlter" datasource="default">
							ALTER TABLE #params.table# DROP COLUMN #params.field#
						</cfquery>
						<cfdump var="#qCustomAlter#">
						Completed<br />
						<cfif isDefined("params.showDetails")>
							<cfquery name="qCustomSelect" datasource="default">
								SELECT * from #params.table#;
							</cfquery>
							<br />
							<cfdump var="#qCustomSelect#" label="Test Data">						
						</cfif>	
						<cfcatch type="any">
							<cfdump var="#cfcatch#"><cfabort>
						</cfcatch>				
						</cftry>
					</cfif>
					
				</cfif>--->
			</cfif>
		</cfsavecontent>
		
		<cfset renderPage(template="/shared/singleOutput") />
	</cffunction>
			
	<cffunction name="createTables">
		
		<cfsavecontent variable="data">
			
			<cfif isDefined("params.players")>
				Creating 'Players' table...
				<cfquery name="qCreatePlayers" datasource="default">
					-- Create 'players' table
					DROP TABLE IF EXISTS Players;
					CREATE TABLE Players 
						(
							PlayerID int identity primary key, 
							FirstName varchar(255), 
							LastName varchar(255),							
							Created timestamp default CURRENT_TIMESTAMP()		
						);
						
					INSERT INTO Players 
							(FirstName, LastName) 
						VALUES 	
							('Joey', 'Daly');
				</cfquery>
				Completed<br />
				<cfif isDefined("params.showDetails")>
					<cfquery name="qSelectPlayers" datasource="default">
						SELECT * from Players;
					</cfquery>
					<br />
					<cfdump var="#qSelectPlayers#" label="Test Data">
				</cfif>
			</cfif>
			
			<cfif isDefined("params.teams")>
				Creating 'Teams' table...
				<cfquery name="qCreateTeams" datasource="default">
					-- Create 'teams' table
					DROP TABLE IF EXISTS Teams;
					CREATE TABLE Teams 
						(
							TeamID int identity primary key, 
							SeasonID int not null,
							TeamName varchar(255),
							GamesFor int not null,
							GamesAgainst int not null,
							Points int not null,
							Created timestamp default CURRENT_TIMESTAMP()
							
						);
						
					INSERT INTO Teams (SeasonID,TeamName) VALUES (1,'A Team');
					INSERT INTO Teams (SeasonID,TeamName) VALUES (1,'B Team');
					INSERT INTO Teams (SeasonID,TeamName) VALUES (1,'C Team');
					INSERT INTO Teams (SeasonID,TeamName) VALUES (1,'D Team');
					INSERT INTO Teams (SeasonID,TeamName) VALUES (1,'E Team');
					INSERT INTO Teams (SeasonID,TeamName) VALUES (1,'F Team');
				</cfquery>
				Completed<br />
				<cfif isDefined("params.showDetails")>
					<cfquery name="qSelectTeams" datasource="default">
						SELECT * from Teams;
					</cfquery>
					<br />
					<cfdump var="#qSelectTeams#" label="Test Data">
				</cfif>
			</cfif>
			
			<cfif isDefined("params.teamplayers")>
				Creating 'TeamPlayers' table...
				<cfquery name="qCreateTeamPlayers" datasource="default">
					-- Create 'TeamPlayers' table
					DROP TABLE IF EXISTS TeamPlayers;
					CREATE TABLE TeamPlayers 
						(
							TeamPlayerID int identity primary key, 
							TeamID int not null,
							PlayerID int not null,
							Position int,
							Created timestamp default CURRENT_TIMESTAMP()
						);
						
					INSERT INTO TeamPlayers 
							(TeamID,PlayerID) 
						VALUES 	
							(1,1);
				</cfquery>
				Completed<br />
				<cfif isDefined("params.showDetails")>
					<cfquery name="qSelectTeamPlayers" datasource="default">
						SELECT * from TeamPlayers;
					</cfquery>
					<br />
					<cfdump var="#qSelectTeamPlayers#" label="Test Data">
				</cfif>
			</cfif>	
			
			
			<cfif isDefined("params.fixtures")>
				Creating 'Fixtures' table...
				<cfquery name="qCreateFixtures" datasource="default">
					-- Create 'Fixtures' table
					DROP TABLE IF EXISTS Fixtures;
					CREATE TABLE Fixtures 
						(
							FixtureID int identity primary key, 
							SeasonID int not null,
							RoundNo int not null,
							RoundDate datetime,
							HomeTeamID int not null,
							HomeTeamName varchar(255),							
							AwayTeamID int not null,	
							AwayTeamName varchar(255),	
							Created timestamp default CURRENT_TIMESTAMP()							
						);
						
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (1,1,6);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (1,3,4);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (1,5,2);
					
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (2,2,3);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (2,4,1);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (2,6,5);
					
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (3,1,2);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (3,3,5);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (3,6,4);
					
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (4,2,4);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (4,3,6);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (4,5,1);
					
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (5,1,3);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (5,4,5);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (5,6,2);
					
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (6,6,1);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (6,4,3);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (6,2,5);
					
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (7,3,2);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (7,1,4);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (7,5,6);
					
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (8,2,1);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (8,5,3);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (8,4,6);
					
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (9,4,2);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (9,6,3);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (9,1,5);
					
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (10,3,1);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (10,5,4);
					--INSERT INTO Fixtures (RoundNo,HomeTeamID,AwayTeamID) VALUES (10,2,6);
					
				</cfquery>
				Completed<br />
				<cfif isDefined("params.showDetails")>
					<cfquery name="qSelectFixtures" datasource="default">
						SELECT * from Fixtures;
					</cfquery>
					<br />
					<cfdump var="#qSelectFixtures#" label="Test Data">
				</cfif>
			</cfif>
			
			<cfif isDefined("params.results")>
				Creating 'Results' table...
				<cfquery name="qCreateResults" datasource="default">
					-- Create 'Results' table
					DROP TABLE IF EXISTS Results;
					CREATE TABLE Results 
						(
							ResultID int identity primary key, 
							FixtureID int not null,
							SeasonID int not null,
							HomeTeamPoints int not null,
							AwayTeamPoints int not null,
							HomeTeamGames int not null,
							AwayTeamGames int not null,
							Created timestamp default CURRENT_TIMESTAMP()
						);					
					
				</cfquery>
				Completed<br />
				<cfif isDefined("params.showDetails")>
					<cfquery name="qSelectResults" datasource="default">
						SELECT * from Results;
					</cfquery>
					<br />
					<cfdump var="#qSelectResults#" label="Test Data">
				</cfif>
			</cfif>	
			
			<cfif isDefined("params.seasons")>
				Creating 'Seasons' table...
				<cfquery name="qCreateSeasons" datasource="default">
					-- Create 'Seasons' table
					DROP TABLE IF EXISTS Seasons;
					CREATE TABLE Seasons 
						(
							SeasonID int identity primary key, 
							SeasonName varchar(255), 
							NoRounds int not null default 10,
							NoTeams int not null default 6,
							SkipWeeks varchar(255),
							StartDate datetime,
							EndDate datetime,
							FixtureCreated datetime,
							Created timestamp default CURRENT_TIMESTAMP()		
						);
						
					--INSERT INTO Seasons 
					--		(SeasonName) 
					--	VALUES 	
					--		('Summer 2011');
				</cfquery>
				Completed<br />
				<cfif isDefined("params.showDetails")>
					<cfquery name="qSelectSeasons" datasource="default">
						SELECT * from Seasons;
					</cfquery>
					<br />
					<cfdump var="#qSelectSeasons#" label="Test Data">
				</cfif>
			</cfif>
			
			
			<cfif isDefined("params.ladders")>
				Creating 'Ladders' table...
				<cfquery name="qCreateLadders" datasource="default">
					-- Create 'Ladders' table
					DROP TABLE IF EXISTS Ladders;
					CREATE TABLE Ladders 
						(
							LadderID int identity primary key, 
							SeasonID int,
							TeamID int, 
							GamesFor int,
							GamesAgainst int,
							Points int,							
							Updated datetime							
						);
						
					--INSERT INTO Ladders 
					--		(SeasonName) 
					--	VALUES 	
					--		('Summer 2011');
				</cfquery>
				Completed<br />
				<cfif isDefined("params.showDetails")>
					<cfquery name="qSelectLadders" datasource="default">
						SELECT * from Ladders;
					</cfquery>
					<br />
					<cfdump var="#qSelectSeasons#" label="Test Data">
				</cfif>
			</cfif>

			<cfif isDefined("params.PlayerResults")>
				Creating PlayerResults table...
				<cfquery name="qCreatePlayerResults" datasource="default">
					-- Create 'PlayerResults' table
					DROP TABLE IF EXISTS PlayerResults;
					CREATE TABLE PlayerResults 
						(
							PlayerResultID int identity primary key, 
							FixtureID int not null,
							SeasonID int not null,
							HomePlayerID int not null,
							AwayPlayerID int not null,
							HomePlayerGames int,
							AwayPlayerGames int,
							Resulted datetime,
							Created timestamp default CURRENT_TIMESTAMP()
						);					
					
				</cfquery>
				Completed<br />
				<cfif isDefined("params.showDetails")>
					<cfquery name="qSelectPlayerResults" datasource="default">
						SELECT * from PlayerResults;
					</cfquery>
					<br />
					<cfdump var="#qSelectPlayerResults#" label="Test Data">
				</cfif>
			</cfif>
			
		</cfsavecontent>
		
		<cfset renderPage(template="/shared/singleOutput") />
		
	</cffunction>
</cfcomponent>